package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class SchoolDao {
    private static SchoolDao schoolDao = null;
    private SchoolDao(){}
    //返回本类唯一的对象
    public static SchoolDao getInstance(){
        //静态变量schoolDao为null时，才创建SchoolDao对象
        //第二次或后来调用本方法schoolDao就不指向null了。不在创建对象
        if(schoolDao == null){
            schoolDao = new SchoolDao();
        }
        return schoolDao;
    }
    public Collection<School> findAll(List<Condition> conditionList, Pagination pagination) throws SQLException, SQLException {
        //创建集合类对象，用来保存所有元组代表的School对象
        //TreeSet按compare(Object o)方法指定的顺序排列
        //HashSet不保证结果中元素的顺序
        Connection connection = JdbcHelper.getConn();
        Collection<School> desiredSchools = new HashSet<>();
        //创建查询school的主语句,StringBuilder可以进行字符串的更改
        StringBuilder sql_select = new StringBuilder("select * from school");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause).append(" ");
        }
        //获取School中的元组总数
        int totalNum = CommonDao.getInstance().count(connection,"school");
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);

        //准备预编译的语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译的语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            //获得school表中的当前元组的id字段的值
            Integer id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Integer uni_id = resultSet.getInt("uni_id");
            University university = UniversityDao.find(uni_id);
            //以当前元组的各个字段值为参数，创建School对象
            School desiredSchool = new School(id,description,no,remarks,university);
            //将当前元组代表的School对象添加到desiredSchool中
            desiredSchools.add(desiredSchool);
        }
        JdbcHelper.close(resultSet,preparedStatement,connection);
        return desiredSchools;
    }
}
